package faceattendsystem.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import faceattendsystem.dao.IEmployeeDao;
import faceattendsystem.entity.Employee;
import faceattendsystem.entity.Mechanism;
import faceattendsystem.entity.PageBean;
import faceattendsystem.util.JDBCTemplate;
import faceattendsystem.util.JDBCTemplate.PreparedStatementSetter;
import faceattendsystem.util.JDBCTemplate.ResultSetCallBack;



public class EmployeeDaoImpl implements IEmployeeDao {

	@Override
	public int add(Employee employee) {
		String sql = "insert into employee values(?,?,?,?,?,?,?,?)";
		
		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, employee.getMechanism().getMechid());
				pstmt.setString(2, employee.getEmpid());
				pstmt.setString(3, employee.getPassword());
				pstmt.setString(4, employee.getName());
				pstmt.setString(5, employee.getIdnumber());
				pstmt.setString(6, employee.getPower());
				pstmt.setString(7, employee.getPicmd5());
				pstmt.setString(8, employee.getState());
			}
		});

		return result;
	}

	@Override
	public int delete(String empid) {
		String sql = "delete from employee where empid=?";

		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, empid);
			}
		});

		return result;
	}

	@Override
	public int update(Employee employee) {
		String sql = "update employee set mechid=?,name=?,idnumber=?,picmd5=?,power=?,state=? where empid=?";

		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, employee.getMechanism().getMechid());
				pstmt.setString(2, employee.getName());
				pstmt.setString(3, employee.getIdnumber());
				pstmt.setString(4, employee.getPicmd5());
				pstmt.setString(5, employee.getPower());
				pstmt.setString(6, employee.getState());
				pstmt.setString(7, employee.getEmpid());
			}
		});

		return result;
	}
	
	@Override
	public int updatePw(String empid,String npw) {
		String sql = "update employee set password=? where empid=?";
		
		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, npw);
				pstmt.setString(2, empid);
			}
		});
		
		return result;
	}

	@Override
	public Employee queryByEmpid(String empid) {
		String sql = "select * from employee where empid=?";
		
		Employee employee = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, empid);
			}
		},new ResultSetCallBack<Employee>() {
			@Override
			public Employee processRs(ResultSet rs) throws SQLException {
				Employee employee = new Employee();
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				employee.setMechanism(mechanism);
				employee.setEmpid(rs.getString(2));
				employee.setPassword(rs.getString(3));
				employee.setName(rs.getString(4));
				employee.setIdnumber(rs.getString(5));
				employee.setPower(rs.getString(6));
				employee.setPicmd5(rs.getString(7));
				employee.setState(rs.getString(8));
				return employee;
			}
		});
		
		return employee;
	}

	@Override
	public PageBean<Employee> queryByMechid(String mechid, int pc, int ps) {
		PageBean<Employee> pb = new PageBean<Employee>();
		pb.setPc(pc);
		pb.setPs(ps);
		
		String sql = "select count(*) from employee where mechid=?";
		
		Number num = (Number)JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});
		int tr=1;
		if(num!=null){
			tr=num.intValue();
		}
		pb.setTr(tr);
		
		sql = "select * from employee where mechid=? order by empid asc";
		
		List<Employee> employeeList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		},new ResultSetCallBack<Employee>() {
			@Override
			public Employee processRs(ResultSet rs) throws SQLException {
				Employee employee = new Employee();
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				employee.setMechanism(mechanism);
				employee.setEmpid(rs.getString(2));
				employee.setPassword(rs.getString(3));
				employee.setName(rs.getString(4));
				employee.setIdnumber(rs.getString(5));
				employee.setPower(rs.getString(6));
				employee.setPicmd5(rs.getString(7));
				employee.setState(rs.getString(8));
				return employee;
			}
		});
		
		pb.setBeanList(employeeList);
		return pb;
	}

	@Override
	public List<Employee> queryAll() {
		String sql = "select * from employee where power=1";
		
		List<Employee> employeeList = JDBCTemplate.query(sql, null, new ResultSetCallBack<Employee>() {
			@Override
			public Employee processRs(ResultSet rs) throws SQLException {
				Employee employee = new Employee();
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				employee.setMechanism(mechanism);
				employee.setEmpid(rs.getString(2));
				employee.setPassword(rs.getString(3));
				employee.setName(rs.getString(4));
				employee.setIdnumber(rs.getString(5));
				employee.setPower(rs.getString(6));
				employee.setPicmd5(rs.getString(7));
				employee.setState(rs.getString(8));
				return employee;
			}
		});
				
		return employeeList;
	}

	@Override
	public PageBean<Employee> query(Employee employee, int pc, int ps) {
		PageBean<Employee> pb = new PageBean<Employee>();
		pb.setPc(pc);
		pb.setPs(ps);
		
		String mechid = employee.getMechanism().getMechid();
		String empid = employee.getEmpid();
		String name = employee.getName();
		String idnumber = employee.getIdnumber();
		String sql = "select count(*) from employee where mechid like '%"+mechid+"%' and empid like '%"+empid+"%' and name like '%"+name+"%' and idnumber like '%"+idnumber+"%' and power=1";
		
		Number num = JDBCTemplate.singleQuery(sql, null, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});
		
		int tr = 1;
		if(num!=null){
			tr=num.intValue();
		}
		pb.setTr(tr);
		
		sql = "select * from employee where mechid like '%"+mechid+"%' and empid like '%"+empid+"%' and name like '%"+name+"%' and idnumber like '%"+idnumber+"%' and power=1 order by mechid,empid asc limit ?,?";
		List<Employee> employeeList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setInt(1, (pc-1)*ps);
				pstmt.setInt(2, ps);
			}
		}, new ResultSetCallBack<Employee>() {
			@Override
			public Employee processRs(ResultSet rs) throws SQLException {
				Employee employee = new Employee();
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				employee.setMechanism(mechanism);
				employee.setEmpid(rs.getString(2));
				employee.setPassword(rs.getString(3));
				employee.setName(rs.getString(4));
				employee.setIdnumber(rs.getString(5));
				employee.setPower(rs.getString(6));
				employee.setPicmd5(rs.getString(7));
				employee.setState(rs.getString(8));
				return employee;
			}
		});
		
		pb.setBeanList(employeeList);
		return pb;
	}

	@Override
	public List<Employee> queryByMechid(String mechid) {
		String sql = "select * from employee where mechid=? order by empid";
		
		List<Employee> employeeList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		}, new ResultSetCallBack<Employee>() {
			@Override
			public Employee processRs(ResultSet rs) throws SQLException {
				Employee employee = new Employee();
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				employee.setMechanism(mechanism);
				employee.setEmpid(rs.getString(2));
				employee.setPassword(rs.getString(3));
				employee.setName(rs.getString(4));
				employee.setIdnumber(rs.getString(5));
				employee.setPower(rs.getString(6));
				employee.setPicmd5(rs.getString(7));
				employee.setState(rs.getString(8));
				return employee;
			}
		});
				
		return employeeList;
	}

	@Override
	public Employee queryByIdnumber(String idnumber) {
		String sql = "select * from employee where idnumber=?";
		
		Employee employee = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, idnumber);
			}
		},new ResultSetCallBack<Employee>() {
			@Override
			public Employee processRs(ResultSet rs) throws SQLException {
				Employee employee = new Employee();
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				employee.setMechanism(mechanism);
				employee.setEmpid(rs.getString(2));
				employee.setPassword(rs.getString(3));
				employee.setName(rs.getString(4));
				employee.setIdnumber(rs.getString(5));
				employee.setPower(rs.getString(6));
				employee.setPicmd5(rs.getString(7));
				employee.setState(rs.getString(8));
				return employee;
			}
		});
		
		return employee;
	}
}
